package zjitc.net.utils.Dao;

import org.apache.commons.dbutils.BaseResultSetHandler;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import zjitc.net.utils.utils.C3P0Utils;
import zjitc.net.utils.utils.Dbutils;
import zjitc.net.utils.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao implements Dao{


    @Override
    public int add(Student student){
        String sql="insert into Student value(?,?,?,?)";
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            connection=Dbutils.getConnection();
            statement=connection.prepareStatement(sql);
            statement.setInt(1,student.getId());
            statement.setString(2,student.getName());
            statement.setInt(3,student.getAge());
            statement.setString(4,student.getSex());
            statement.executeUpdate();


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Dbutils.close(connection,statement,resultSet);
        }


        return 1;
    }



    @Override
    public List<Student> findall()throws Exception {
        QueryRunner runner =new QueryRunner(C3P0Utils.getDateSource());
        String sql="select * from Student;";
        List<Student> list =(List)runner.query(sql, new BeanListHandler(Student.class));

        return list;
       /* List<Student> list=new ArrayList<>();
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
             connection=Dbutils.getConnection();
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();
            while(resultSet.next()){
                Student s=new Student();
                s.setId(resultSet.getByte(1));
                s.setName(resultSet.getString(2));
                s.setAge(resultSet.getByte(3));
                s.setSex(resultSet.getString(4));
                list.add(s);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Dbutils.close(connection,statement,resultSet);
        }*/
       }

    @Override
    public Student findbyid(int id) throws Exception {
        QueryRunner runner =new QueryRunner(C3P0Utils.getDateSource());
        String sql="select * from Student where id="+id+";";
        Student student1=(Student) runner.query(sql, new BeanHandler(Student.class));

        return student1;

    }

    @Override
    public int modify(Student student) throws SQLException {
        QueryRunner runner =new QueryRunner(C3P0Utils.getDateSource());
        String sql="update student set id=?,age=?,sex=? where name=?;";
        runner.update(sql,new Object[]{student.getId(),student.getAge(),student.getSex(),student.getName()});



        /* Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            connection=Dbutils.getConnection();
            statement=connection.prepareStatement(sql);
            statement.setInt(1,student.getId());
            statement.setInt(2,student.getAge());
            statement.setString(3,student.getSex());
            statement.setString(4,student.getName());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Dbutils.close(connection,statement,resultSet);
        }
*/
        return 1;
    }

    @Override
    public int del(String name) throws SQLException {
        String sql="delete from student where name=?;";
        QueryRunner runner =new QueryRunner(C3P0Utils.getDateSource());
        return runner.update(sql,name);

        /* Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            connection=Dbutils.getConnection();
            statement=connection.prepareStatement(sql);
            statement.setString(1,name);

            } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Dbutils.close(connection,statement,resultSet);
                        }
        return 1;
        */

    }
    public int  length(int n,int... parme){
        for (int i = 0; i <parme.length ; i++) {
            System.out.println(parme[i]);

            }
        return parme.length;
    }
}
